﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using System.IO;
using System.Collections;
using System.Windows.Forms;

namespace 数据同步 {
    /// <summary>
    /// 数据库同步
    /// </summary>
    public class SyncNavigator {

        public bool TABLE = false;
        public bool COLUMN = false;
        public bool FUNCTION = false;
        public bool VIEW = false;
        public bool TRIGGER = false;
        public bool PROCEDURE = false;

        private string[] noStr = new string[] { "sqldatapaging", "" };
        private StringBuilder builder = new StringBuilder();
        public bool RunCode(string con_source, string con_target) {
            var source = new DbHelperSQLP(con_source);
            var target = new DbHelperSQLP(con_target);
            builder = new StringBuilder();

            #region 表字段
            builder.AppendLine("--=========================" + source.GetDbName() + " 表字段====================================");
            builder.AppendLine($"SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON ");
            builder.AppendLine($"GO ");
            builder.AppendLine($"SET NUMERIC_ROUNDABORT OFF ");
            builder.AppendLine($"GO ");

            var tables = source.GetTables();
            foreach(var table in tables) {
                Console.ForegroundColor = ConsoleColor.White;
                Console.WriteLine("******源**" + source.GetDbName() + "**" + table.TableName + "************");
                if(target.IsSysObjects(table.TableName)) {

                    if(table.TableName.StartsWith("Detail_Wages_Import_")) {
                        continue;
                    }
                    Console.ForegroundColor = ConsoleColor.White;
                    Console.WriteLine("******目标**" + target.GetDbName() + "**" + table.TableName + "************");
                    if(COLUMN) {
                        #region 添加、修改字段
                        var source_columns = source.GetColumnInfoList(table.TableName);
                        var target_col = target.GetColumnInfoList(table.TableName);
                        if(source_columns.Count() != target_col.Count()) {
                            Console.ForegroundColor = ConsoleColor.Red;
                            Console.WriteLine("****数据字段数不相等************");

                            var srGorup = source_columns.Where(x => !target_col.Any(a => x.ColumnName.ToLower() == a.ColumnName.ToLower() && x.ColumnType == a.ColumnType)).ToList();

                            if(srGorup.Count > 0) {
                                builder.AppendLine("--" + table.TableName + " 数据字段数不相等");
                                foreach(var item in srGorup) {
                                    var len = "";
                                    if(item.MaxLength > 0) {
                                        len = item.MaxLength.ToString();
                                    } else {
                                        len = "max";
                                    }
                                    var tc = target_col.Find(e => e.ColumnName.ToLower() == item.ColumnName.ToLower());
                                    if(tc != null) {
                                        if(tc.MaxLength != item.MaxLength) {
                                            #region 修改字段
                                            builder.AppendLine($"PRINT N'目标{target.GetDbName()}库修改{table.TableName}->{item.ColumnName}' ").AppendLine("GO");
                                            if(item.ColumnType == "int" || item.ColumnType == "datetime") {
                                                builder.AppendLine("ALTER TABLE " + table.TableName + " ALTER COLUMN [" + item.ColumnName + "] " + item.ColumnType + " null ").AppendLine("GO");
                                            } else if(item.ColumnType == "decimal") {
                                                builder.AppendLine("ALTER TABLE " + table.TableName + " ALTER COLUMN [" + item.ColumnName + "] " + item.ColumnType + "(" + len + ",2) null ").AppendLine("GO");
                                            } else {
                                                builder.AppendLine("ALTER TABLE " + table.TableName + " ALTER COLUMN [" + item.ColumnName + "] " + item.ColumnType + "(" + len + ")  null ").AppendLine("GO");
                                            }
                                            #endregion
                                        }
                                    } else {

                                        Console.ForegroundColor = ConsoleColor.Red;
                                        Console.WriteLine("******目标**" + target.GetDbName() + "**不存在**" + table.TableName + "->" + item.ColumnName + "************");
                                        #region MyRegion
                                        builder.AppendLine($"PRINT N'目标{target.GetDbName()}库不存在{table.TableName}->{item.ColumnName}' ").AppendLine("GO");
                                        builder.AppendLine("if not exists (select * from syscolumns where id = OBJECT_ID('" + table.TableName + "') and [name]='" + item.ColumnName + "')");
                                        if(item.ColumnType == "decimal") {
                                            builder.AppendLine("ALTER TABLE " + table.TableName + " ADD [" + item.ColumnName + "] decimal(" + item.MaxLength + "," + item.Scale + ") null");
                                        } else if(item.ColumnType == "int" || item.ColumnType == "datetime") {
                                            builder.AppendLine("ALTER TABLE " + table.TableName + " ADD [" + item.ColumnName + "] " + item.ColumnType + " null");
                                        } else {
                                            builder.AppendLine("ALTER TABLE " + table.TableName + " ADD [" + item.ColumnName + "] " + item.ColumnType + "(" + len + ")  null ");
                                        }
                                        builder.AppendLine("GO");

                                        #endregion
                                    }
                                }
                            }
                        }
                        #endregion
                    }
                } else {
                    if(TABLE) {
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine("******目标**" + target.GetDbName() + "**不存在**" + table.TableName + "************");
                        builder.AppendLine("--******目标**" + target.GetDbName() + "**不存在**" + table.TableName + "************");
                        builder.AppendLine("PRINT N'Creating " + table.TableName + " table'  ").AppendLine("GO");

                        builder.AppendLine($"PRINT N'目标{target.GetDbName()}库不存在{table.TableName}' ").AppendLine("GO");

                        builder.AppendLine($"if exists (select * from sysobjects where id = OBJECT_ID('[{table.TableName}]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)  ");
                        builder.AppendLine($"DROP TABLE [{table.TableName}] ");
                        builder.AppendLine($"CREATE TABLE [{table.TableName}]( ");
                        var source_columns = source.GetColumnInfoList(table.TableName);
                        foreach(var item in source_columns) {
                            builder.Append("[" + item.ColumnName + "] [" + item.ColumnType + "] ");
                            if(item.IsIdentity) {
                                builder.Append(" IDENTITY (1, 1) ");
                            }
                            string len = "";
                            if(item.MaxLength > 0) {
                                len = item.MaxLength.ToString();
                            } else {
                                len = "max";
                            }
                            switch(item.ColumnType.ToLower()) {
                                case "char":
                                case "nchar":
                                case "binary":
                                case "nvarchar":
                                case "varbinary":
                                case "varchar":
                                case "text":
                                case "ntext":
                                    builder.Append(" (" + len + ")");
                                    break;
                                case "decimal":
                                case "numeric":
                                    builder.Append(" (" + item.Precision + "," + item.Scale + ")");
                                    break;
                            }
                            if(item.IsNullable) {
                                builder.Append(" NULL");
                            } else {
                                builder.Append(" NOT NULL");
                            }
                            if(!string.IsNullOrWhiteSpace(item.DefaultValue)) {
                                builder.Append(" DEFAULT " + item.DefaultValue);
                            }
                            builder.AppendLine(",");
                        }
                        builder.DelLastComma();
                        builder.AppendLine($") ");
                        builder.AppendLine($"GO ");
                    }
                }
            }
            #endregion

            #region 函数 FN
            if(FUNCTION) {
                builder.AppendLine("--=========================" + source.GetDbName() + " 函数====================================");
                var source_FN = source.ExecuteTable("select name from sysobjects where xtype = 'FN'");
                if(source_FN != null && source_FN.Rows.Count > 0) {
                    foreach(DataRow tr in source_FN.Rows) {
                        string tr_name = tr["name"].ToString();
                        if(!noStr.Contains(tr_name.ToLower())) {

                            string scriptSql = source.GetSingle<string>($"select [definition] from sys.sql_modules where object_id=object_id('{tr_name}')");
                            SetStr(ScriptType.FUNCTION, tr_name, $"目标{target.GetDbName()}库创建{tr_name}函数 ", scriptSql);
                        }
                    }
                }
            }
            #endregion

            #region 触发器 TR
            if(TRIGGER) {
                builder.AppendLine("--=========================" + source.GetDbName() + " 触发器====================================");
                var source_TR = source.ExecuteTable("select name from sysobjects where xtype = 'TR'");
                if(source_TR != null && source_TR.Rows.Count > 0) {
                    foreach(DataRow tr in source_TR.Rows) {
                        string tr_name = tr["name"].ToString();
                        if(!noStr.Contains(tr_name.ToLower())) {
                            string scriptSql = source.GetSingle<string>($"select [definition] from sys.sql_modules where object_id=object_id('{tr_name}')");

                            SetStr(ScriptType.TRIGGER, tr_name, $"目标{target.GetDbName()}库创建{tr_name}触发器 ", scriptSql);
                        }

                    }
                }
            }
            #endregion

            #region 试图 V
            if(VIEW) {
                builder.AppendLine("--=========================" + source.GetDbName() + " 试图====================================");
                var source_V = source.ExecuteTable("select name from sysobjects where xtype = 'V'");
                if(source_V != null && source_V.Rows.Count > 0) {
                    foreach(DataRow tr in source_V.Rows) {
                        string tr_name = tr["name"].ToString();
                        if(!noStr.Contains(tr_name.ToLower())) {
                            string scriptSql = source.GetSingle<string>($"select [definition] from sys.sql_modules where object_id=object_id('{tr_name}')");

                            SetStr(ScriptType.VIEW, tr_name, $"目标{target.GetDbName()}库创建{tr_name}试图 ", scriptSql);

                        }
                    }
                }
            }
            #endregion

            #region 存储过程 P
            if(PROCEDURE) {
                builder.AppendLine("--=========================" + source.GetDbName() + " 存储过程====================================");
                var source_P = source.ExecuteTable("select name from sysobjects where xtype = 'P'");
                if(source_P != null && source_P.Rows.Count > 0) {
                    foreach(DataRow tr in source_P.Rows) {
                        string tr_name = tr["name"].ToString();
                        if(!noStr.Contains(tr_name.ToLower())) {
                            string scriptSql = source.GetSingle<string>($"select [definition] from sys.sql_modules where object_id=object_id('{tr_name}')");

                            SetStr(ScriptType.PROCEDURE, tr_name, $"目标{target.GetDbName()}库创建{tr_name}存储过程 ", scriptSql);
                        }
                    }
                }
            }
            #endregion

            if(builder.Length > 1) {
                builder.AppendLine($"PRINT 'Script deployment completed' ");
                builder.AppendLine($"GO ");
                Console.ForegroundColor = ConsoleColor.Green;
                Console.WriteLine("******源**" + source.GetDbName() + "**目标**" + target.GetDbName() + "************");
                string dir = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
                File.AppendAllText(dir + "\\从【" + source.GetDbName() + "】库同步到【" + target.GetDbName() + "】库人事数据结构.sql", builder.ToString());
                return true;
            } else {
                Console.WriteLine("****没有要同步的数据************");
            }
            return false;
        }
        public enum ScriptType {

            FUNCTION,

            TRIGGER,

            VIEW,

            PROCEDURE

        }
        public void SetStr(ScriptType myEnum, string name, string print, string scriptSql) {
            builder.AppendLine("PRINT N' " + print + " ' ").AppendLine("GO");
            switch(myEnum) {
                case ScriptType.FUNCTION:
                    builder.AppendLine($"IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID('[{name}]') and xtype = 'FN')  ");
                    break;
                case ScriptType.TRIGGER:
                    builder.AppendLine($"IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID('[{name}]') and xtype = 'TR')  ");
                    break;
                case ScriptType.VIEW:
                    builder.AppendLine($"IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID('[{name}]') and xtype = 'V')  ");
                    break;
                case ScriptType.PROCEDURE:
                    builder.AppendLine($"IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID('[{name}]') and xtype = 'P')  ");
                    break;
            }
            builder.AppendLine($"DROP {myEnum} [{name}] ").AppendLine("GO");
            builder.AppendLine(scriptSql.DelNote()).AppendLine("GO");
            builder.AppendLine("IF (@@ERROR <> 0) AND (@@TRANCOUNT > 0) ROLLBACK TRANSACTION ");
            builder.AppendLine("GO ");
            builder.AppendLine("IF @@TRANCOUNT > 0 ");
            builder.AppendLine("  COMMIT TRANSACTION ");
            builder.AppendLine("GO ");
            builder.AppendLine("IF @@TRANCOUNT = 0 BEGIN TRANSACTION ");
            builder.AppendLine("GO ");
        }
    }
}
